﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WebShopData
{
    public class User
    {
        public User(string userName, string passWord, string firstName, string lastName, string address, string city, string zip, string email, string status = "Active", int userId = 0)
        {
            UserId = userId;
            UserName = userName;
            PassWord = passWord;
            FirstName = firstName;
            LastName = lastName;
            Address = address;
            City = city;
            Zip = zip;
            Email = email;
            Status = status;
        }

        public int UserId { get; set; }
        public string UserName { get; set; }
        public string PassWord { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string Zip { get; set; }
        public string Email { get; set; }
        public string Status { get; set; }

        public override string ToString()
        {
            return string.Format("{0} {1} {2} {3} ", UserId, UserName, FirstName, LastName);
        }
    }

    public static class UserDB
    {
        public static int InsertNewUser(User user)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("INSERT INTO [dbo].[Users]([UserName],[PassWord],[FirstName],[Lastname],[Address],[Zip],[City],[Email],[Status])");
                    sqlQuery.Append(" VALUES(@UserName, @Password, @FirstName, @LastName, @Address, @Zip, @City, @Email, @Status)");
                    sqlQuery.Append(" SELECT @@IDENTITY");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("UserName", SqlDbType.VarChar, 50).Value = user.UserName;
                    command.Parameters.Add("PassWord", SqlDbType.VarChar, 50).Value = user.PassWord;
                    command.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value = user.FirstName;
                    command.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value = user.LastName;
                    command.Parameters.Add("Address", SqlDbType.VarChar, 50).Value = user.Address;
                    command.Parameters.Add("Zip", SqlDbType.VarChar, 50).Value = user.Zip;
                    command.Parameters.Add("City", SqlDbType.VarChar, 50).Value = user.City;
                    command.Parameters.Add("Email", SqlDbType.VarChar, 100).Value = user.City;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = user.Status;

                    int identity = int.Parse(command.ExecuteScalar().ToString());
                    return identity;
                }
            }
        }

        public static List<User> GetAllUsers()
        {
            return GetUsersBase("", new List<SqlParameter>());
        }

        public static User GetUserById(int userId)
        {
            string whereLine = " WHERE [UserId] = @UserId";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("UserId", SqlDbType.VarChar, 50);
            sqlParameter.Value = userId;
            parameters.Add(sqlParameter);
            List<User> users = GetUsersBase(whereLine, parameters);
            if (users.Count == 1)
            {
                return users[0];
            }
            return null;
        }

        public static User GetUserByUserName(string userName)
        {
            string whereLine = " WHERE [UserName] = @UserName";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("UserName", SqlDbType.VarChar, 50);
            sqlParameter.Value = userName;
            parameters.Add(sqlParameter);
            List<User> users = GetUsersBase(whereLine, parameters);
            if (users.Count == 1)
            {
                return users[0];
            }
            return null;
        }

        private static List<User> GetUsersBase(string whereLine, List<SqlParameter> parameters)
        {
            DataSet dataSetUsers = GetUsersAsDataSet(whereLine, parameters);
            var users = new List<User>();
            foreach (DataRow row in dataSetUsers.Tables[0].Rows)
            {
                users.Add(new User(
                    userName: row["UserName"].ToString().Trim(),
                    passWord: row["PassWord"].ToString().Trim(),
                    firstName: row["FirstName"].ToString().Trim(),
                    lastName: row["LastName"].ToString().Trim(),
                    address: row["Address"].ToString().Trim(),
                    zip: row["Zip"].ToString().Trim(),
                    city: row["City"].ToString().Trim(),
                    email: row["Email"].ToString().Trim(),
                    status: row["Status"].ToString().Trim(),
                    userId: int.Parse(row["UserId"].ToString().Trim())));
            }
            return users;
        }

        public static DataSet GetUsersAsDataSet(string whereLine, List<SqlParameter> parameters)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("SELECT [UserId],[UserName],[PassWord],[FirstName],[LastName],[Address],[Zip],[City],[Email],[Status]");
                    sqlQuery.Append(" FROM [dbo].[UsersNotDeletedView]");
                    sqlQuery.Append(whereLine);
                    command.CommandText = sqlQuery.ToString();
                    foreach (SqlParameter sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                    DataSet result = GalaxySale2014DB.GetDataSet(command);
                    return result;
                }
            }
        }

        public static int UpdateUser(User user)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("UPDATE [dbo].[Users]");
                    sqlQuery.Append(" SET [UserName] = @UserName");
                    sqlQuery.Append(", [PassWord] = @PassWord");
                    sqlQuery.Append(", [FirstName] = @FirstName");
                    sqlQuery.Append(", [LastName] = @LastName");
                    sqlQuery.Append(", [Address] = @Address");
                    sqlQuery.Append(", [Zip] = @Zip");
                    sqlQuery.Append(", [City] = @City");
                    sqlQuery.Append(", [Email] = @Email");
                    sqlQuery.Append(", [Status] = @Status");
                    sqlQuery.Append(" WHERE [UserId] = @UserId");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("UserName", SqlDbType.VarChar, 50).Value = user.UserName;
                    command.Parameters.Add("PassWord", SqlDbType.VarChar, 50).Value = user.PassWord;
                    command.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value = user.FirstName;
                    command.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value = user.LastName;
                    command.Parameters.Add("Address", SqlDbType.VarChar, 50).Value = user.Address;
                    command.Parameters.Add("Zip", SqlDbType.VarChar, 10).Value = user.Zip;
                    command.Parameters.Add("City", SqlDbType.VarChar, 50).Value = user.City;
                    command.Parameters.Add("Email", SqlDbType.VarChar, 50).Value = user.Email;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = user.Status;
                    command.Parameters.Add("UserId", SqlDbType.Int).Value = user.UserId;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static int DeleteUserById(int userId)
        {
            User user = GetUserById(userId);
            user.Status = "Deleted";
            return UpdateUser(user);
            //using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            //{
            //    using (SqlCommand command = connection.CreateCommand())
            //    {
            //        var sqlQuery = new StringBuilder("DELETE FROM [dbo].[Users]");
            //        sqlQuery.Append(" WHERE [UserId] = @UserId");
            //        command.CommandText = sqlQuery.ToString();

            //        command.Parameters.Add("UserId", SqlDbType.Int).Value = userId;

            //        int affectedRows = command.ExecuteNonQuery();
            //        return affectedRows;
            //    }
            //}
        }
    }
}